/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pro.dao;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import pro.bean.Link;

/**
 *
 * @author Cyndi
 */

/**
 *
 * @author Niko
 */
public class LinkDAO extends Dbase{

    public void registrarlink(Link link) throws SQLException {
        PreparedStatement pstmt = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb");
            conn.setAutoCommit(false);
            String sql = "insert into link_budget"
                    + " values (?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, nuevoId());
            pstmt.setString(2, link.getDesc());
            pstmt.setFloat(3, link.getFrec());
            pstmt.setFloat(4, link.getDist());
            pstmt.setInt(5, link.getIdProyecto());
            pstmt.executeUpdate();
            conn.commit();
        } finally {
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
    }
    
    public Integer nuevoId() throws SQLException {
        this.init_mysql();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Integer nuevoId = null;
        try {

            conn.setAutoCommit(false);
            String sql = "SELECT (CASE WHEN max(idlink_budget) IS NULL THEN 0 ELSE max(idlink_budget) END) + 1 FROM link_budget";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                nuevoId = rs.getInt(1);
                nuevoId = nuevoId == null ? 1 : nuevoId;
            }
        } finally {
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return nuevoId;
    }    
    
}
